﻿Imports System.Data.SqlClient

Public Class QuyetToan
    Private sDk As String
    Private sSoQt As String
    Private dTuNgay As Date
    Private dDenNgay As Date
    Private sMaDv As String
    Private sLyDo As String

    Public Property Dk() As String
        Get
            Return sDk
        End Get
        Set(ByVal Value As String)
            sDk = Value
        End Set
    End Property

    Public Property SoQt() As String
        Get
            Return sSoQt
        End Get
        Set(ByVal Value As String)
            sSoQt = Value
        End Set
    End Property

    Public Property TuNgay() As Date
        Get
            Return dTuNgay
        End Get
        Set(ByVal Value As Date)
            dTuNgay = Value
        End Set
    End Property

    Public Property DenNgay() As Date
        Get
            Return dDenNgay
        End Get
        Set(ByVal Value As Date)
            dDenNgay = Value
        End Set
    End Property

    Public Property MaDv() As String
        Get
            Return sMaDv
        End Get
        Set(ByVal Value As String)
            sMaDv = Value
        End Set
    End Property

    Public Property LyDo() As String
        Get
            Return sLyDo
        End Get
        Set(ByVal Value As String)
            sLyDo = Value
        End Set
    End Property

End Class

Public Class QuyetToanF
    Public Function Get_DonVi() As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Get_DonVi"

            .Parameters.Add(New SqlClient.SqlParameter("@nam", SqlDbType.Int))
            .Parameters("@nam").Value = Project_Nam

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Get_Last() As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Get_Last"

            .Parameters.Add(New SqlClient.SqlParameter("@nam", SqlDbType.Int))
            .Parameters("@nam").Value = Project_Nam

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Get_Filter(ByVal MaDv As String) As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Get_Filter"

            .Parameters.Add(New SqlClient.SqlParameter("@madv", SqlDbType.Char))
            .Parameters("@madv").Value = MaDv

            .Parameters.Add(New SqlClient.SqlParameter("@nam", SqlDbType.Int))
            .Parameters("@nam").Value = Project_Nam

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Exists_SoQt(ByVal SoQt As String) As Boolean
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Exists_SoQt"

            .Parameters.Add(New SqlClient.SqlParameter("@soqt", SqlDbType.Char))
            .Parameters("@soqt").Value = SoQt

            .Parameters.Add(New SqlClient.SqlParameter("@return", SqlDbType.Bit)).Direction = ParameterDirection.ReturnValue
            .ExecuteNonQuery()
        End With

        If myCmd.Parameters("@return").Value = 1 Then
            myCnn.Close()
            myCnn.Dispose()
            Return True
        Else
            myCnn.Close()
            myCnn.Dispose()
            Return False
        End If
    End Function

    Public Function Save(ByVal QuyetToan As QuyetToan) As Boolean
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Save"

            .Parameters.Add(New SqlClient.SqlParameter("@dk", SqlDbType.Char))
            .Parameters("@dk").Value = QuyetToan.Dk

            .Parameters.Add(New SqlClient.SqlParameter("@soqt", SqlDbType.Char))
            .Parameters("@soqt").Value = QuyetToan.SoQt

            .Parameters.Add(New SqlClient.SqlParameter("@tungay", SqlDbType.SmallDateTime))
            .Parameters("@tungay").Value = QuyetToan.TuNgay

            .Parameters.Add(New SqlClient.SqlParameter("@denngay", SqlDbType.SmallDateTime))
            .Parameters("@denngay").Value = QuyetToan.DenNgay

            .Parameters.Add(New SqlClient.SqlParameter("@madv", SqlDbType.Char))
            .Parameters("@madv").Value = QuyetToan.MaDv

            .Parameters.Add(New SqlClient.SqlParameter("@lydo", SqlDbType.NVarChar))
            .Parameters("@lydo").Value = QuyetToan.LyDo

            .Parameters.Add(New SqlClient.SqlParameter("@return", SqlDbType.Bit)).Direction = ParameterDirection.ReturnValue
            .ExecuteNonQuery()
        End With

        If myCmd.Parameters("@return").Value = 1 Then
            myCnn.Close()
            myCnn.Dispose()
            Return True
        Else
            myCnn.Close()
            myCnn.Dispose()
            Return False
        End If
    End Function

    Public Function Delete(ByVal SoQt As String) As Boolean
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Delete"

            .Parameters.Add(New SqlClient.SqlParameter("@soqt", SqlDbType.Char))
            .Parameters("@soqt").Value = SoQt

            .Parameters.Add(New SqlClient.SqlParameter("@return", SqlDbType.Bit)).Direction = ParameterDirection.ReturnValue
            .ExecuteNonQuery()
        End With

        If myCmd.Parameters("@return").Value = 1 Then
            myCnn.Close()
            myCnn.Dispose()
            Return True
        Else
            myCnn.Close()
            myCnn.Dispose()
            Return False
        End If
    End Function

    Public Function Get_SoQt(ByVal soqt As String) As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Get_SoQt"

            .Parameters.Add(New SqlClient.SqlParameter("@soqt", SqlDbType.Char))
            .Parameters("@soqt").Value = soqt
        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Get_ThiCong(ByVal soqt As String) As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Get_ThiCong"

            .Parameters.Add(New SqlClient.SqlParameter("@soqt", SqlDbType.Char))
            .Parameters("@soqt").Value = soqt
        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Get_ChietTinh_Filter(ByVal soqt As String, ByVal sotc As String) As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Get_ChietTinh_Filter"

            .Parameters.Add(New SqlClient.SqlParameter("@soqt", SqlDbType.Char))
            .Parameters("@soqt").Value = soqt

            .Parameters.Add(New SqlClient.SqlParameter("@sotc", SqlDbType.Char))
            .Parameters("@sotc").Value = sotc

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Get_ChietTinh(ByVal soqt As String) As DataTable
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Get_ChietTinh"

            .Parameters.Add(New SqlClient.SqlParameter("@soqt", SqlDbType.Char))
            .Parameters("@soqt").Value = soqt
        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb
        myCnn.Close()
    End Function

    Public Function Update_ChietTinh(ByVal soqt As String, ByVal sophieu As String) As Boolean
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Update_ChietTinh"

            .Parameters.Add(New SqlClient.SqlParameter("@soqt", SqlDbType.Char))
            .Parameters("@soqt").Value = soqt

            .Parameters.Add(New SqlClient.SqlParameter("@sophieu", SqlDbType.Char))
            .Parameters("@sophieu").Value = sophieu

            .Parameters.Add(New SqlClient.SqlParameter("@return", SqlDbType.Bit)).Direction = ParameterDirection.ReturnValue
            .ExecuteNonQuery()
        End With

        If myCmd.Parameters("@return").Value = 1 Then
            myCnn.Close()
            myCnn.Dispose()
            Return True
        Else
            myCnn.Close()
            myCnn.Dispose()
            Return False
        End If
    End Function

    Public Function Get_Count_Ma(ByVal Soqt As String) As Integer
        Dim myProjCnn As New ProjectConnection
        Dim myCnn As New SqlConnection
        Dim myDat As New SqlDataAdapter
        Dim myDtb As New DataTable

        myCnn = myProjCnn.GetConnection
        myCnn.Open()

        Dim myCmd As New SqlCommand
        With myCmd
            .Connection = myCnn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "QuyetToan_Get_Count_Ma"

            .Parameters.Add(New SqlClient.SqlParameter("@soqt", SqlDbType.Char))
            .Parameters("@soqt").Value = Soqt

        End With

        myDat = New SqlDataAdapter(myCmd)
        myDat.Fill(myDtb)
        Return myDtb.Rows(0).Item("slma")
        myCnn.Close()
        myCnn.Dispose()
    End Function
End Class

